package org.soenea.doitall.domain.model.user.data;

import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.dsrg.soenea.service.logging.SQLLogger;
import org.dsrg.soenea.service.threadLocal.DbRegistry;

public class UserTDG {
	public static final String BASE_NAME = "user";
	public static final String TABLE = DbRegistry.getTablePrefix()+BASE_NAME;
	
	public static final String INSERT = "INSERT INTO " + TABLE + "(userID, userName, firstName, lastName,password,groupID,version,admin) VALUES(?, ?, ?, ?,?,?,?,?);";
	public static final String UPDATE = "UPDATE " + TABLE + " AS u set u.version=u.version+1, u.userName=?, u.firstName=?, u.lastName=?, u.password=?, u.groupID=?, u.admin=? WHERE u.userID=? AND u.version=?;";	
		
	public final static String CREATE_TABLE = 
			"CREATE TABLE IF NOT EXISTS " + TABLE + " (" +
			"userID BIGINT,"+
			"userName VARCHAR(64),"+
			"password VARCHAR(64),"+
			"firstName VARCHAR(64),"+
			"lastName VARCHAR(64),"+
			"groupID BIGINT,"+
			"version INT,"+
			"admin BOOLEAN,"+
			"PRIMARY KEY(userID)"+
			") ENGINE=InnoDB;";
			  
	public final static String DROP_TABLE = "DROP TABLE IF EXISTS " + TABLE + ";";

	public static void createTable() throws SQLException {
		SQLLogger.processUpdate(DbRegistry.getDbConnection().createStatement(), CREATE_TABLE);
	}
	
	public static void dropTable() throws SQLException {
		SQLLogger.processUpdate(DbRegistry.getDbConnection().createStatement(), DROP_TABLE);
	}
	
	public static int update(long userID, long version, String userName, String firstName, String lastName, String password, long groupID, boolean admin) throws SQLException {
		PreparedStatement ps = DbRegistry.getDbConnection().prepareStatement(UPDATE);
		ps.setString(1, userName);
		ps.setString(2, firstName);
		ps.setString(3, lastName);
		ps.setString(4, password);
		ps.setLong(5, groupID);
		ps.setBoolean(6, admin);
		ps.setLong(7, userID);
		ps.setLong(8, version);
		ps.setBoolean(6, admin);
		ps.execute();		
		
		int count = ps.getUpdateCount();
		ps.close();
		return count;
	}

	public static void insert(long userID, String userName, String firstName,
			String lastName, String password, long groupID, long version, boolean admin)throws SQLException {

		PreparedStatement ps = DbRegistry.getDbConnection().prepareStatement(INSERT);
		ps.setLong(1, userID);
		ps.setString(2, userName);
		ps.setString(3, firstName);
		ps.setString(4, lastName);
		ps.setString(5, password);
		ps.setLong(6, groupID);
		ps.setLong(7, version);
		ps.setBoolean(8, admin);
		ps.execute();
		ps.close();
		
	}
	
}
